Data Import and Export

Most data in General Ledger Managercan be imported and exported. You can import data such as account codes in a chart of accounts or trial balances that you'll use in the creation of a master trial balance. You can export data such as a master trial balance for use in other applications. For large records volume, such as for account maps, you can also export data so that you can edit it outside the system and then import the new version of the data.

Data you can import and export

The following table shows the data that you can import and export in General Ledger Manager.

File Format

Files of data can be imported and exported in various file formats, depending upon the area of General Ledger Manager in which you are working. The following list describes the file formats used by each area of the application.

• You can import data in Setup in either.xls or .xlsx files, but you can export data from Setup in .xlsx files only.
• You can add trial balances to the Import Queue in Import Trial Balances in .xls, .xlsx, or .csv files. If you use ONESOURCE Excel Add-inA locally installed add-in for Microsoft Excel Professional that enables you to enter data into certain ONESOURCE applications using linked Excel worksheets instead of using the interfaces of the applications. In fact, the ONESOURCE applications do not even have to be open for you to add data to them using this add-in. to upload trial balance data to the Import Queue, they appear the queue as .xml files. You can download trial balances from the Import Queue only in the file format in which they were uploaded. If trial balance data is valid, you can import it into General Ledger Manager.

• You can export trial balances from Review Trial Balances in .xlsx files only.
• You can export master trial balances as .csv files. Master trial balance support files can be exported in Excel format.

Note: Imports of files in .xls format are limited to 65, 536 rows of data for a worksheet.

Importing data

Data can be imported in Setup either by using an Excel file that is based upon an import template or one that is not based strictly on a template. Trial balance data can be imported into General Ledger Manager after you upload valid trial balance files to the Import Queue in Import Trial Balances. Validation results appear automatically in the Format Validation column of the Import Queue grid.

Each type of data import has different requirements as discussed below.

Importing Setup Data

Data entered in Setup modules can be imported using .xlsx template files. A similar template is available for importing adjustment data for a trial balance in Review Trial Balance. A Download Import Template link appears in the pages in which you can import data. The procedure is to download the template, populate the file with your with data, and then import it. Though a unique template is available in each view in Setup in which you can import data as well as in Review Trial Balance, you can also import the same data using Excel files that are not strictly based on import templates. Regardless of whether you use a template, import data files must adhere to certain requirements.

Requirements for template-based Imports of Setup data and trial balance adjustment data

1. Download the import template for the data that you want to import by clicking Download Import Template in the appropriate view. For example, when creating a chart of accounts, you can click Download Import Template in step 1 of Create Chart of Accounts to download the template for importing account codes. Import templates are in.xlsx format. They are designed to be populated with data that is specific to the view in which they are downloaded.
2. When entering data in an import template, follow these rules:

• Leave the existing column headers in place in the template file exactly as they are on row 1, and enter the appropriate data under each column header beginning on row 2. For example, if the header for column A of the import template is AccountCode, enter account codes you want to import in column A, starting on row 2.
• Enter the data in the first worksheet only. The only exception to this rule is in importing account mapping data in Account Maps.If you're importing account/master account mapping relationships, additional worksheets must be used for reporting unit exceptionsA reporting unit exception is a separate section of an account map that can be used to specify alternative account/master account relationships that should be applied for specific reporting units. For example in the Primary section of the map, you can specify that account 100 from chart of accounts A should be mapped to master account 1000. This would apply to all reporting units for which a balance of account 100 in that chart is provided. However, you can set up a reporting unit exeception in the same map that specifies that any balances of account 100 for reporting unit Z should be summed instead under master account 200. When an account map is applied to combine trial balances into a master trial balance, the alternative mapping in a reporting unit exception is also be applied if source trial balances for its reporting units are present..
• Always enter the data that you want to import starting on row 2 of the import template. The first blank row in an import file is interpreted as the end of the data being imported. Therefore if you leave row 2 blank, an error message appears when you import the template file informing you that there is no data to process in the workbook, and no data is imported.
• Enter all of the data that is required by the record type you're importing. For example, if you are importing a file of reporting unit-to-entity code mapping relationships for a reporting unit rollup, you must include one reporting unit for each entity. In cases of importing mapping data, such as that of a reporting unit rollup, every element, such as reporting unit code and entity code, must be valid or errors will be produced. .
• Do not change the name of the worksheets of the template. Worksheet names are part of the template format. If you're importing account mapping data, the first worksheet must be named Primary, and the worksheets for reporting unit exceptions must be named Reporting Unit Exception 1, Reporting Unit Exception 2 and so on.

3. Save the file in .xlsx or .xls format.

NOTE 1: If you delete or change the column headings in a template-based import file, or change the primary worksheet name and then try to import the file, the data will still be imported, but starting from row 1. See the requirements for non-template imports below.

NOTE 2: If multiple records in a Setup data file contain the same account code or reporting unit code, only the last record containing that code will be imported. For example, if you're importing account codes for a chart of accounts your data contains two records for account 100, the first with description A and the second with description B, the record that appears in the chart after the import will be account 100 with a description of B.

Requirements for Non-template Imports of Setup Data

While it is recommended that you use the templates provided for importing data in Setup, you can import data from Excel files that are not strictly based on a template. However, non-template import files must meet the following requirements:

• The data to be imported must appear in the first worksheet of the Excel file. The only exception to this rule is in importing account mapping data in Account Maps where you may need to enter data in additional worksheets for each reporting unit exceptionA reporting unit exception is a separate section of an account map that can be used to specify alternative account/master account relationships that should be applied for specific reporting units. For example in the Primary section of the map, you can specify that account 100 from chart of accounts A should be mapped to master account 1000. This would apply to all reporting units for which a balance of account 100 in that chart is provided. However, you can set up a reporting unit exeception in the same map that specifies that any balances of account 100 for reporting unit Z should be summed instead under master account 200. When an account map is applied to combine trial balances into a master trial balance, the alternative mapping in a reporting unit exception is also be applied if source trial balances for its reporting units are present..
• The data that is required by the record type you're importing must be entered in the Excel file with the same column order as the template for that data type.The table above shows the data elements that are required for each type of data import as well as the columns of the Excel worksheet in which those elements must appear.
• Enter the data that you want to import beginning on row 1 of the Excel worksheet.
• Do not leave any blank rows in the data you want to import. The first blank row in an import file is interpreted as the end of the data being imported. Therefore, a blank row could cause a data file to be only partially imported without any warning to this effect.

Importing Trial Balance Data

Importing trial balance files is a two-step process in which you must first upload trial balance files to the Import Queue and then import valid files in the queue into General Ledger Manager. You can upload files to the Import Queue either manually by using the Add a File to Queue function or automatically by using General Ledger Manager connect. Click here for more information about General Ledger Manager connect.

Requirements for trial balance files that you upload to the Import Queue

NOTE: This information does not apply to data uploaded via Excel Add-in.

Each trial balance file that you upload must have:

• A file format of .csv, .xls, or .xlsx.
• Only one trial balance.
• Records that each contain a Reporting Unit/Entity code, GL Account Code, and Ending Balance. Optionally, a Beginning Balance can also be included. Trial balances should be represent data for a month, a year, or date-year-to-date.
• Data beginning on Row 2 in the file.
• No blank rows. The first blank row in an import file is interpreted as the end of the data being imported.

Requirements for trial balance files that you import from the Import Queue

• Must be a valid file and without formatting errors.
• Must not have any unknown account codes.

Exporting data

All data, with the exception of master trial balances, is exported in .xlsx file format. If an export exceeds 1 million rows in the primary worksheet, it will be broken down into multiple files containing no more than one million rows. The resulting file set is then exported in a .zip file. If exports of account mapping data are broken into multiple files, each file contains the same arrangement of worksheets to accommodate reporting unit exceptions.